/**
 * 需要用户登录的路由
 */

import { Router } from "express";
const router = Router();

import db from "../database.js";
import { v4 as uuid } from "uuid";
import dayjs from "dayjs";
// 导入登录验证和参数验证中间件
import { auth, upload, validate } from "../middlewares.js";
// 导入参数验证器
import validators from "../validate.js";
import { isEmpty, orderBy, writeFile, deleteFile } from "../utils.js";
import path from "node:path";
import { verifyJWT } from "../jwt.js";

import { makePassword, checkPassword } from "../utils.js";

// router 全局验证登录状态
router.use(auth);

// 获取 userId
const getUserId = async (req) => {
  const { authorization } = req.headers;
  const payload =
    authorization &&
    (await verifyJWT(
      authorization.startsWith("Bearer ")
        ? authorization.slice(7)
        : authorization
    ));
  const userId = payload?.userId ?? req.session.userId;
  return userId;
};

router.post("/user/test", async (req, res) => {
  console.log("userId", await getUserId(req));
});

// 新建便签
router.post("/user/notes", async (req, res) => {
  const userId = await getUserId(req);
  const { content } = req.body;
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const noteId =
    await db.insert`insert into Notes (content, createTime,personId) values(${content}, ${createTime}, ${userId})`;
  const note = await db.findOne`select * from Notes where id = ${noteId}`;
  res.send({
    success: true,
    message: "创建便签成功!",
    data: note,
  });
});

// 获取所有便签
router.post("/user/notesByPagination", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 10, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from Notes where personId = ${userId}`;
  const notes =
    await db.findAll`select * from Notes where personId = ${userId}  order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取便签成功!",
    data: notes,
    total: total,
  });
});
// 获取便签总数
router.get("/user/notes/count", async (req, res) => {
  try {
    const userId = await getUserId(req);
    const { total } =
      await db.findOne`select count(*) AS total from Notes where personId = ${userId}`;
    res.send({
      success: true,
      message: "获取便签总数成功!",
      total: total,
    });
  } catch (error) {
    res.status(500).send({
      success: false,
      message: "服务器内部错误",
    });
  }
});

// 修改便签
router.put("/user/notes/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  const { content } = req.body;
  const exist =
    await db.findOne`select * from Notes where id = ${id} and personId = ${userId}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "便签不存在!",
    });
  }
  await db.run`update Notes set content = ${content} where id = ${id}`;
  const note = await db.findOne`select * from Notes where id = ${id}`;
  return res.send({
    success: true,
    message: "修改便签成功!",
    data: note,
  });
});

// 删除便签
router.delete("/user/notes/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  const exist =
    await db.findOne`select * from Notes where id = ${id} and personId = ${userId}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "便签不存在!",
    });
  }
  await db.run`delete from Notes where id = ${id}`;
  return res.send({
    success: true,
    message: "删除便签成功!",
  });
});

// 新增任务
router.post("/user/tasks", async (req, res) => {
  const userId = await getUserId(req);
  const { content } = req.body;
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const noteId =
    await db.insert`insert into Tasks (content, createTime,personId) values(${content}, ${createTime}, ${userId})`;
  const task = await db.findOne`select * from Tasks where id = ${noteId}`;
  res.send({
    success: true,
    message: "创建任务成功!",
    data: task,
  });
});

// 获取所有任务
router.post("/user/tasksByPagination", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 5, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from Tasks where personId = ${userId}`;
  const tasks =
    await db.findAll`select * from Tasks where personId = ${userId} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取任务成功!",
    data: tasks,
    total: total,
  });
});
// 获取任务总数
router.get("/user/tasks/count", async (req, res) => {
  try {
    const userId = await getUserId(req);
    const { total } =
      await db.findOne`select count(*) AS total from Notes where personId = ${userId}`;
    res.send({
      success: true,
      message: "获取便签总数成功!",
      total: total,
    });
  } catch (error) {
    res.status(500).send({
      success: false,
      message: "服务器内部错误",
    });
  }
});

// 删除任务
router.delete("/user/tasks/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  const exist =
    await db.findOne`select * from Tasks where id = ${id} and personId = ${userId}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "任务不存在!",
    });
  }
  await db.run`delete from Tasks where id = ${id}`;
  return res.send({
    success: true,
    message: "删除任务成功!",
  });
});

// 修改任务
router.put("/user/tasks/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  const { content } = req.body;
  const exist =
    await db.findOne`select * from Tasks where id = ${id} and personId = ${userId}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "任务不存在!",
    });
  }
  await db.run`update Tasks set content = ${content} where id = ${id}`;
  const task = await db.findOne`select * from Tasks where id = ${id}`;
  return res.send({
    success: true,
    message: "修改任务成功!",
    data: task,
  });
});

// 完成任务
router.put("/user/tasks/finish/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  const exist =
    await db.findOne`select * from Tasks where id = ${id} and personId = ${userId}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "任务不存在!",
    });
  } else if (exist.finishTime) {
    return res.send({
      success: false,
      message: "任务已完成!",
    });
  }
  const finishTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  await db.run`update Tasks set finishTime = ${finishTime} where id = ${id}`;
  const task = await db.findOne`select * from Tasks where id = ${id}`;
  return res.send({
    success: true,
    message: "完成任务成功!",
    data: task,
  });
});

// 新增通讯录
// router.post("/user/contact", async (req, res) => {
//   const userId = await getUserId(req);
//   const { name, phone, org, duty, email } = req.body;
//   const contactId =
//     await db.insert`insert into Contacts (name, phone,org,duty, email,personId) values(${name}, ${phone}, ${org}, ${duty}, ${email}, ${userId})`;
//   const contact =
//     await db.findOne`select * from Contacts where id = ${contactId}`;
//   res.send({
//     success: true,
//     message: "创建联系人成功!",
//     data: contact,
//   });
// });

// 获取当前登录用户通讯录
router.get("/user/contact", async (req, res) => {
  const userId = await getUserId(req);
  const contact =
    await db.findOne`select * from Person t1, Contacts t2 where t1.id = t2.personId and personId = ${userId}`;
  res.send({
    success: true,
    message: "获取联系人成功!",
    data: contact,
  });
});

// 获取所有姓名不为空且不包含自己的用户的通讯录
router.post("/user/contactsByPagination", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 3, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from Person t1, Contacts t2 where t1.id = t2.personId and t1.id != ${userId} and t1.name is not null and demission = "否"`;
  const contacts =
    await db.findAll`select * from Person t1, Contacts t2 where t1.id = t2.personId and t1.id != ${userId} and t1.name is not null and demission = "否" limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取所有联系人成功!",
    data: contacts,
    total: total,
  });
});
// 模糊查询通讯录中的姓名
router.get("/user/contacts/:name", async (req, res) => {
  const { name } = req.params;
  const userId = await getUserId(req);
  const contacts =
    await db.findAll`select * from Person t1, Contacts t2 where t1.id = t2.personId and demission = "否" and personId != ${userId} and name like ${`%${name}%`}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: contacts,
  });
});
// 模糊查询通讯录中的姓名
router.post("/user/contacts/:name", async (req, res) => {
  const { name } = req.params;
  const { limit = 3, offset = 0 } = req.body;
  const userId = await getUserId(req);
  const { total } =
    await db.findOne`select count(*) as total from Person t1, Contacts t2 where t1.id = t2.personId and t1.id != ${userId} and t1.name is not null and demission = "否" and name = ${name}`;
  const contacts =
    await db.findAll`select * from Person t1, Contacts t2 where t1.id = t2.personId and demission = "否" and personId != ${userId} and name like ${`%${name}%`} limit ${limit} offset ${offset}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: contacts,
    total: total,
  });
});

// 修改自己的通讯录
router.put("/user/contact", async (req, res) => {
  const userId = await getUserId(req);
  const { name, phone, email, address } = req.body;
  // 修改 Person 表对应属性
  await db.run`update Person set name = ${name} where id = ${userId}`;
  // 修改 Contacts 表对应属性
  await db.run`update Contacts set phone = ${phone}, email = ${email}, address = ${address} where personId = ${userId}`;
  const contact =
    await db.findOne`select * from Person t1, Contacts t2 where t1.id = t2.personId and t1.id = ${userId}`;
  res.send({
    success: true,
    message: "修改联系人成功!",
    data: contact,
  });
});

// 判断是否有权限
const hasPermission = async (userId, org) => {
  const user = await db.findOne`select * from Person where id = ${userId}`;
  if (user.org.toString().includes(org)) {
    return true;
  } else {
    return false;
  }
};

// 新建公文
router.post("/user/document", async (req, res) => {
  const userId = await getUserId(req);
  const { title, content, type } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const documentId =
    await db.insert`insert into Documents (title, content,createTime, type, processed,status, personId) values(${title}, ${content}, ${createTime}, ${type},"是","待审批", ${userId})`;
  const document =
    await db.findOne`select * from Documents where id = ${documentId}`;
  res.send({
    success: true,
    message: "创建公文成功!",
    data: document,
  });
});
// 获取公文
router.post("/user/documentByPagination", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 4, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where personId = ${userId}`;
  const documents =
    await db.findAll`select * from documents  where personId = ${userId} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取所有公文成功!",
    data: documents,
    total: total,
  });
});
// 管理层获取所有公文
router.post("/user/document/all", async (req, res) => {
  const { limit = 3, offset = 0 } = req.body;
  const { total } = await db.findOne`select count(*) as total from documents`;
  const documents =
    await db.findAll`select * from documents order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取所有公文成功!",
    data: documents,
    total: total,
  });
});
// 获取 通知 类型公文
router.post("/user/document/tz", async (req, res) => {
  const type = "通知";
  const { limit = 4, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where type = ${type}`;
  const documents =
    await db.findAll`select * from documents where type = ${type} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取通知类型公文成功!",
    data: documents,
    total: total,
  });
});
// 获取 批复 类型公文
router.post("/user/document/pf", async (req, res) => {
  const type = "批复";
  const { limit = 4, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where type = ${type}`;
  const documents =
    await db.findAll`select * from documents where type = ${type} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取批复类型公文成功!",
    data: documents,
    total: total,
  });
});
// 获取 请示 类型公文
router.post("/user/document/qs", async (req, res) => {
  const type = "请示";
  const { limit = 4, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where type = ${type}`;
  const documents =
    await db.findAll`select * from documents where type = ${type} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取请示类型公文成功!",
    data: documents,
    total: total,
  });
});
// 获取 其他 类型公文
router.post("/user/document/qt", async (req, res) => {
  const type = "其他";
  const { limit = 4, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where type = ${type}`;
  const documents =
    await db.findAll`select * from documents where type = ${type} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取其他类型公文成功!",
    data: documents,
    total: total,
  });
});
// 修改公文
router.put("/user/document/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { title, content, type } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { id } = req.params;
  await db.run`update Documents set title = ${title}, content = ${content}, type = ${type} where id = ${id}`;
  const document = await db.findOne`select * from Documents where id = ${id}`;
  res.send({
    success: true,
    message: "修改公文成功!",
    data: document,
  });
});

// 获取所有待审批的公文
router.post("/user/not_approved_documents", async (req, res) => {
  const userId = await getUserId(req);
  const org = "管理层";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }

  // 审批三种状态为：已通过、未通过、待审批
  const { limit = 3, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where status = '待审批'`;
  const documents =
    await db.findAll`select * from Documents where status = '待审批' order by id desc  limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取公文成功!",
    data: documents,
    total: total,
  });
});

// 获取所有未通过审批的公文
router.post("/user/not_passed_documents", async (req, res) => {
  const userId = await getUserId(req);
  const org = "管理层";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }

  // 审批三种状态为：已通过、未通过、待审批
  const { limit = 3, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where status = '未通过'`;
  const documents =
    await db.findAll`select * from Documents where status = '未通过' order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取公文成功!",
    data: documents,
    total: total,
  });
});

// 获取所有已通过审批的公文
router.post("/user/approved_documents", async (req, res) => {
  const userId = await getUserId(req);
  const org = "管理层";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }

  // 审批三种状态为：已通过、未通过、待审批
  const { limit = 3, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from documents where status = '已通过'`;
  const documents =
    await db.findAll`select * from Documents where status = '已通过' order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取公文成功!",
    data: documents,
    total: total,
  });
});

// 删除公文
router.delete("/user/document/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  await db.run`delete from Documents where id = ${id}`;
  res.send({
    success: true,
    message: "删除公文成功!",
  });
});

// 公文审批
router.put("/user/documents/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 审批三种状态为：已通过、未通过、待审批
  // 如果未通过，传过来的原因
  const { status, reason } = req.body;

  // 判断权限
  const org = "管理层";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const exist =
    await db.findOne`select * from Documents where id = ${id} and status = '待审批'`;
  if (!exist) {
    return res.send({
      success: false,
      message: "公文不存在或已审批!",
    });
  }

  // 根据审核是否通过，做不同处理
  if (status === "已通过") {
    await db.run`update Documents set status = ${status} where id = ${id}`;
    const document = await db.findOne`select * from Documents where id = ${id}`;
    return res.send({
      success: true,
      message: "审批完成!",
      data: document,
    });
  } else {
    await db.run`update Documents set status = ${status}, reason = ${reason} where id = ${id}`;
    const document = await db.findOne`select * from Documents where id = ${id}`;
    return res.send({
      success: true,
      message: "审批完成!",
      data: document,
    });
  }
});

// 发布新闻
router.put("/user/news", async (req, res) => {
  const userId = await getUserId(req);
  const { title, content } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const newId =
    await db.insert`insert into News(title, content, createTime,personId) values(${title}, ${content}, ${createTime}, ${userId})`;
  const news = await db.findOne`select * from News where id = ${newId}`;

  // 将该新闻插入到全局信息里面
  const post = await db.findOne`select * from Person where id = ${userId}`;
  const globalInfoId =
    await db.insert`insert into GlobalInfo (type, title, content, createTime, path, postId, postName, avatar) values("新闻", ${title}, ${content}, ${createTime},"/public_information_module/Information", ${userId}, ${post?.name}, "https://img.js.design/assets/img/6593626c56fd7ef975496d79.png#457c260719ac67ea741d650ab800af48"`;

  // 将未读信息插入到 GlobalInfoRead 表中，为每个员工插入一条对应的记录，设置初始值为"未读"
  const personList = await db.findAll`select id from Person`;
  personList.map((person) => {
    db.insert`insert into GlobalInfoRead (globalInfoId, personId, isRead) values(${globalInfoId}, ${person.id}, "未读")`;
  });

  res.send({
    success: true,
    message: "发布新闻成功!",
    data: news,
  });
});

// 删除新闻
router.delete("/user/news/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const exist = await db.findOne`select * from News where id = ${id}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "新闻不存在!",
    });
  }
  await db.run`delete from News where id = ${id}`;
  res.send({
    success: true,
    message: "删除新闻成功!",
  });
});

// 获取所有新闻
router.post("/user/news", async (req, res) => {
  const { limit = 4, offset = 0 } = req.body;
  const { total } = await db.findOne`select count(*) as total from News`;
  const news =
    await db.findAll`select * from News order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取新闻成功",
    data: news,
    total,
  });
});

// 发布通知
router.put("/user/notice", async (req, res) => {
  const userId = await getUserId(req);
  const { title, content } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const noticeId =
    await db.insert`insert into Notices(title, content, createTime,personId) values(${title}, ${content}, ${createTime}, ${userId})`;
  const notice = await db.findOne`select * from Notices where id = ${noticeId}`;

  // 将该通知插入到全局信息里面
  const post = await db.findOne`select * from Person where id = ${userId}`;
  const globalInfoId =
    await db.insert`insert into GlobalInfo (type, title, content, createTime, path, postId, postName, avatar) values("通知", ${title}, ${content}, ${createTime},"/public_information_module/Information", ${userId}, ${post?.name}, "https://img.js.design/assets/img/6593626c666aaa7192b0f5d5.png#44da73809b37c312ded4da1fabbd044e")`;

  // 将未读信息插入到 GlobalInfoRead 表中，为每个员工插入一条对应的记录，设置初始值为"未读"
  const personList = await db.findAll`select id from Person`;
  personList.map((person) => {
    db.insert`insert into GlobalInfoRead (globalInfoId, personId, isRead) values(${globalInfoId}, ${person.id}, "未读")`;
  });

  res.send({
    success: true,
    message: "发布通知成功!",
    data: notice,
  });
});

// 删除通知
router.delete("/user/notice/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const exist = await db.findOne`select * from Notices where id = ${id}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "通知不存在!",
    });
  }
  await db.run`delete from Notices where id = ${id}`;
  res.send({
    success: true,
    message: "删除通知成功!",
  });
});

// 获取所有通知
router.post("/user/notices", async (req, res) => {
  const { limit = 4, offset = 0 } = req.body;
  const { total } = await db.findOne`select count(*) as total from Notices`;
  const notices =
    await db.findAll`select * from Notices order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取通知成功!",
    data: notices,
    total,
  });
});

// 发行期刊
router.put("/user/periodical", async (req, res) => {
  const userId = await getUserId(req);
  const { title, content } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const periodicalId =
    await db.insert`insert into Periodicals(title, content, createTime,personId) values(${title}, ${content}, ${createTime}, ${userId})`;
  const periodical =
    await db.findOne`select * from Periodicals where id = ${periodicalId}`;

  // 将该通知插入到全局信息里面
  const post = await db.findOne`select * from Person where id = ${userId}`;
  const globalInfoId =
    await db.insert`insert into GlobalInfo (type, title, content, createTime, path, postId, postName, avatar) values("期刊", ${title}, ${content}, ${createTime},"/public_information_module/Information", ${userId}, ${post?.name}, "https://img.js.design/assets/img/6593626c4795d2682668adb8.png#70e598c1c71d6d7416180c172eb8e971`;

  // 将未读信息插入到 GlobalInfoRead 表中，为每个员工插入一条对应的记录，设置初始值为"未读"
  const personList = await db.findAll`select id from Person`;
  personList.map((person) => {
    db.insert`insert into GlobalInfoRead (globalInfoId, personId, isRead) values(${globalInfoId}, ${person.id}, "未读")`;
  });

  res.send({
    success: true,
    message: "发布期刊成功!",
    data: periodical,
  });
});

// 删除期刊
router.delete("/user/periodical/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const exist = await db.findOne`select * from Periodicals where id = ${id}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "期刊不存在!",
    });
  }
  await db.run`delete from Periodicals where id = ${id}`;
  res.send({
    success: true,
    message: "删除期刊成功!",
  });
});

// 获取所有期刊
router.post("/user/periodicals", async (req, res) => {
  const { limit = 4, offset = 0 } = req.body;
  const { total } = await db.findOne`select count(*) as total from Periodicals`;
  const periodicals =
    await db.findAll`select * from Periodicals order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取期刊成功!",
    data: periodicals,
    total,
  });
});

// 发布规章制度
router.put("/user/regulation", async (req, res) => {
  const userId = await getUserId(req);
  const { title, content } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const regulationId =
    await db.insert`insert into Regulations(title, content, createTime,personId) values(${title}, ${content}, ${createTime}, ${userId})`;
  const regulation =
    await db.findOne`select * from Regulations where id = ${regulationId}`;

  // 将该规章制度插入到全局信息里面
  const post = await db.findOne`select * from Person where id = ${userId}`;
  const globalInfoId =
    await db.insert`insert into GlobalInfo (type, title, content, createTime, path, postId, postName, avatar) values("规章制度", ${title}, ${content}, ${createTime},"/public_information_module/Information", ${userId}, ${post?.name}, "https://img.js.design/assets/img/6593626ca4c48859ad7bf053.png#a85863afcf2cf9d3c21dcd7ee950ec23`;

  // 将未读信息插入到 GlobalInfoRead 表中，为每个员工插入一条对应的记录，设置初始值为"未读"
  const personList = await db.findAll`select id from Person`;
  personList.map((person) => {
    db.insert`insert into GlobalInfoRead (globalInfoId, personId, isRead) values(${globalInfoId}, ${person.id}, "未读")`;
  });

  res.send({
    success: true,
    message: "发布规章制度成功!",
    data: regulation,
  });
});

// 删除规章制度
router.delete("/user/regulation/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const exist = await db.findOne`select * from Regulations where id = ${id}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "规章制度不存在!",
    });
  }
  await db.run`delete from Regulations where id = ${id}`;
  res.send({
    success: true,
    message: "删除规章制度成功!",
  });
});

// 获取所有规章制度
router.post("/user/regulations", async (req, res) => {
  const { limit = 4, offset = 0 } = req.body;
  const { total } = await db.findOne`select count(*) as total from Regulations`;
  const regulations =
    await db.findAll`select * from Regulations order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取规章制度成功!",
    data: regulations,
    total,
  });
});

// 会议室使用申请
router.put("/user/meetingRoom", async (req, res) => {
  const userId = await getUserId(req);
  let { meetRoomId, beginTime, endTime, applyReason } = req.body;
  // beginTime 和 endTime 接收的是时间戳
  beginTime = dayjs(beginTime).format("YYYY-MM-DD HH:mm:ss");
  endTime = dayjs(endTime).format("YYYY-MM-DD HH:mm:ss");
  // 判断申请的会议室是否被占用
  // 审批三种状态为：已通过、未通过、待审批
  const allApply =
    await db.findAll`select * from MeetingRoomApply where meetRoomId = ${meetRoomId} and  status != "未通过"`;
  for (let i = 0; i < allApply.length; i++) {
    if (
      !(
        dayjs(endTime).valueOf() < dayjs(allApply[i].beginTime).valueOf() ||
        dayjs(beginTime).valueOf() > dayjs(allApply[i].endTime).valueOf()
      )
    ) {
      return res.send({
        success: false,
        message: "会议室已被占用!",
      });
    }
  }
  const applyId =
    await db.insert`insert into MeetingRoomApply(meetRoomId, beginTime, endTime, applyReason, status, personId) values(${meetRoomId}, ${beginTime}, ${endTime}, ${applyReason}, "待审批", ${userId})`;
  const apply =
    await db.findOne`select * from MeetingRoomApply where id = ${applyId}`;
  return res.send({
    success: true,
    message: "申请成功,等待审批!",
    data: apply,
  });
});

// 获取所有会议室
router.get("/user/meetingRooms", async (req, res) => {
  const meetingRooms = await db.findAll`select * from MeetingRooms`;
  res.send({
    success: true,
    message: "获取会议室成功!",
    data: meetingRooms,
  });
});

// 获取所有会议室申请记录
router.post("/user/meetingRoom/all", async (req, res) => {
  const { limit = 4, offset = 0, status = "全部" } = req.body;
  let total = 0;
  let applys = [];
  if (status === "待审批") {
    applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id where a.status = "待审批" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id where a.status = "待审批"`
    ).total;
  } else if (status === "已通过") {
    applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id where a.status = "已通过" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id where a.status = "已通过"`
    ).total;
  } else if (status === "未通过") {
    applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id where a.status = "未通过" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id where a.status = "未通过"`
    ).total;
  } else {
    applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id`
    ).total;
  }
  res.send({
    success: true,
    message: "获取会议室使用记录成功!",
    data: applys,
    total,
  });
});

// 获取登录用户的会议室使用申请记录
router.post("/user/meetingRoom", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 4, offset = 0 } = req.body;
  const { total } =
    await db.findOne`SELECT count(*) as total FROM MeetingRoomApply a
  INNER JOIN MeetingRooms r ON a.meetRoomId = r.id
  WHERE a.personId = ${userId}`;
  const applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id
    WHERE a.personId = ${userId} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取会议室使用申请记录成功!",
    data: applys,
    total,
  });
});

// 会议室使用审批
router.post("/user/meetingRoomApply/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 审批三种状态为：已通过、未通过、待审批
  const { status, reason } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  if (status === "已通过") {
    await db.run`update MeetingRoomApply set status = ${status} where id = ${id}`;
  } else {
    await db.run`update MeetingRoomApply set status = ${status},approvalReason = ${reason} where id = ${id}`;
  }
  const apply =
    await db.findOne`select * from MeetingRoomApply where id = ${id}`;
  console.log(apply);
  return res.send({
    success: true,
    message: "审批完成!",
    data: apply,
  });
});

// 获取待审批会议室
router.get("/user/meetingRoom/spmeet", async (req, res) => {
  const applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id
    WHERE a.status = "待审批"`;
  res.send({
    success: true,
    message: "获取待审批会议室成功!",
    data: applys,
  });
});

// 获取已通过会议室
router.get("/user/meetingRoom/tgmeet", async (req, res) => {
  const applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id
    WHERE a.status = "已通过"`;
  res.send({
    success: true,
    message: "获取待审批会议室成功!",
    data: applys,
  });
});

// 获取未通过会议室
router.get("/user/meetingRoom/ntgmeet", async (req, res) => {
  const applys = await db.findAll`SELECT a.*, r.sn, r.description
    FROM MeetingRoomApply a
    INNER JOIN MeetingRooms r ON a.meetRoomId = r.id
    WHERE a.status = "未通过"`;
  res.send({
    success: true,
    message: "获取待审批会议室成功!",
    data: applys,
  });
});

// 办公用品购买申请
router.post("/user/supplies/apply", async (req, res) => {
  const { supplies, num, applyReason } = req.body;
  const userId = await getUserId(req);
  const applyId =
    await db.insert`insert into OfficeSuppliesApply(supplies, num, applyReason, personId, status) values(${supplies}, ${num}, ${applyReason}, ${userId}, "待审批")`;
  const apply =
    await db.findOne`select * from OfficeSuppliesApply where id = ${applyId}`;
  return res.send({
    success: true,
    message: "申请成功!",
    data: apply,
  });
});
// 获取所有申请
router.post("/user/supplies/all", async (req, res) => {
  const { limit = 11, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from OfficeSuppliesApply`;
  const applys =
    await db.findAll`select * from OfficeSuppliesApply limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取办公用品购买申请记录成功!",
    data: applys,
    total: total,
  });
});
// 获取当前用户的办公用品购买申请记录
router.post("/user/supplies", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 11, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from OfficeSuppliesApply where personId = ${userId}`;
  const applys =
    await db.findAll`select * from  OfficeSuppliesApply where personId = ${userId} limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取办公用品购买申请记录成功!",
    data: applys,
    total: total,
  });
});

// 办公用品购买审批
router.put("/user/supplies/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 审批三种状态为：已通过、未通过、待审批
  const { status, reason } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  if (status === "已通过") {
    await db.run`update  OfficeSuppliesApply set status = ${status} where id = ${id}`;
  } else {
    await db.run`update  OfficeSuppliesApply set status = ${status},approvalReason = ${reason} where id = ${id}`;
  }
  const apply =
    await db.findOne`select * from  OfficeSuppliesApply where id = ${id}`;
  return res.send({
    success: true,
    message: "审批完成!",
    data: apply,
  });
});

// 获取所有的图书
router.get("/user/books", async (req, res) => {
  const books = await db.findAll`select * from Books`;
  res.send({
    success: true,
    message: "获取图书成功!",
    data: books,
  });
});

// 按照书名模糊查询图书
router.get("/user/books/:name", async (req, res) => {
  const { name } = req.params;
  const books =
    await db.findAll`select * from Books where name like ${`%${name}%`}`;
  res.send({
    success: true,
    message: "获取图书成功!",
    data: books,
  });
});

// 图书申请
router.post("/user/books", async (req, res) => {
  const { bookId, applyReason } = req.body;
  const userId = await getUserId(req);
  // 判断是否拥有该书
  const exist = await db.findOne`select * from Books where id = ${bookId}`;
  if (!exist) {
    return res.send({
      success: false,
      message: "该图书不存在",
    });
  }
  // beginTime 和 endTime 接收的是时间戳
  let { beginTime, endTime } = req.body;
  beginTime = dayjs(beginTime).format("YYYY-MM-DD HH:mm:ss");
  endTime = dayjs(endTime).format("YYYY-MM-DD HH:mm:ss");
  // 判断申请的会议室是否被占用
  // 审批三种状态为：已通过、未通过、待审批
  const allApplys =
    await db.findAll`select * from BooksApply where bookId = ${bookId} and status = "待审批" or status = "已通过"`;
  for (let i = 0; i < allApplys.length; i++) {
    if (
      dayjs(allApplys[i].endTime).valueOf() > dayjs(beginTime).valueOf() &&
      dayjs(allApplys[i].beginTime).valueOf() < dayjs(endTime).valueOf()
    ) {
      return res.send({
        success: false,
        message: "图书已被借出",
      });
    }
  }
  const applyId =
    await db.insert`insert into BooksApply(bookId, applyReason, beginTime, endTime, personId, status) values(${bookId}, ${applyReason}, ${beginTime}, ${endTime}, ${userId}, "待审批")`;
  const apply =
    await db.findOne`select * from BooksApply where id = ${applyId}`;
  return res.send({
    success: true,
    message: "申请成功!",
    data: apply,
  });
});

// 获取当前用户所有的图书申请
router.post("/user/books/apply", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 4, offset = 0 } = req.body;
  const { total } = await db.findOne`select count(*) as total from BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where personId = ${userId}`;
  const applys = await db.findAll`select a.*,r.name from BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where personId = ${userId} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取图书申请成功!",
    data: applys,
    total,
  });
});

// 获取所有图书申请
router.post("/user/books/all", async (req, res) => {
  const { limit = 4, offset = 0, status = "全部" } = req.body;
  let total = 0;
  let applys = [];
  if (status === "待审批") {
    applys = await db.findAll`SELECT a.*, r.name
    FROM BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where a.status = "待审批" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where a.status = "待审批"`
    ).total;
  } else if (status === "已通过") {
    applys = await db.findAll`SELECT a.*, r.name
    FROM BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where a.status = "已通过" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where a.status = "已通过"`
    ).total;
  } else if (status === "未通过") {
    applys = await db.findAll`SELECT a.*, r.name
    FROM BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where a.status = "未通过" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from BooksApply a
    INNER JOIN Books r ON a.bookId = r.id where a.status = "未通过"`
    ).total;
  } else {
    applys = await db.findAll`SELECT a.*, r.name
    FROM BooksApply a
    INNER JOIN Books r ON a.bookId = r.id order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from BooksApply a
    INNER JOIN Books r ON a.bookId = r.id`
    ).total;
  }
  res.send({
    success: true,
    message: "获取图书申请记录成功!",
    data: applys,
    total,
  });
});

// 获取某个图书的所有申请
router.get("/user/books/apply/:id", async (req, res) => {
  const { id } = req.params;
  const applys =
    await db.findAll`select * from BooksApply where bookId = ${id}`;
  res.send({
    success: true,
    message: "获取图书申请成功!",
    data: applys,
  });
});

// 图书申请审批
router.put("/user/books/apply/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 审批三种状态为：已通过、未通过、待审批
  const { status, reason } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  if (status === "已通过") {
    await db.run`update BooksApply set status = ${status} where id = ${id}`;
  } else {
    await db.run`update BooksApply set status = ${status},approvalReason = ${reason} where id = ${id}`;
  }
  const apply = await db.findOne`select * from BooksApply where id = ${id}`;
  return res.send({
    success: true,
    message: "审批完成!",
    data: apply,
  });
});

// 获取所有车辆
router.get("/user/cars", async (req, res) => {
  const cars = await db.findAll`select * from Cars`;
  res.send({
    success: true,
    message: "获取车辆成功!",
    data: cars,
  });
});

// 车辆使用申请
router.post("/user/cars", async (req, res) => {
  const { carId, applyReason } = req.body;
  const userId = await getUserId(req);
  // beginTime 和 endTime 接收的是时间戳
  let { beginTime, endTime } = req.body;
  beginTime = dayjs(beginTime).format("YYYY-MM-DD HH:mm:ss");
  endTime = dayjs(endTime).format("YYYY-MM-DD HH:mm:ss");
  // 判断申请的车辆是否被占用
  // 审批三种状态为：已通过、未通过、待审批
  console.log("beginTime", beginTime, "endTime", endTime);
  // 审批三种状态为：已通过、未通过、待审批
  const allApplys =
    await db.findAll`select * from CarsApply where carId = ${carId} and  status != "未通过"`;
  for (let i = 0; i < allApplys.length; i++) {
    if (
      !(
        dayjs(endTime).valueOf() < dayjs(allApplys[i].beginTime).valueOf() ||
        dayjs(beginTime).valueOf() > dayjs(allApplys[i].endTime).valueOf()
      )
    ) {
      return res.send({
        success: false,
        message: "车辆已被占用!",
      });
    }
  }
  const applyId =
    await db.insert`insert into CarsApply(carId, applyReason, beginTime, endTime, personId, status) values(${carId}, ${applyReason}, ${beginTime}, ${endTime}, ${userId}, "待审批")`;
  const apply = await db.findOne`select * from CarsApply where id = ${applyId}`;

  return res.send({
    success: true,
    message: "申请成功!",
    data: apply,
  });
});

// 获取当前用户的车辆使用申请记录
router.post("/user/cars/apply", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 4, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from CarsApply a INNER JOIN Cars b ON a.carId = b.id where personId = ${userId}`;
  const applys =
    await db.findAll`select a.*,b.model,b.plateNumber from CarsApply a INNER JOIN Cars b ON a.carId = b.id where personId = ${userId} order by id desc limit ${limit} offset ${offset}`;
  res.send({
    success: true,
    message: "获取车辆申请成功!",
    data: applys,
    total,
  });
});

// 获取某个车辆的所有申请
router.get("/user/cars/apply/:id", async (req, res) => {
  const { id } = req.params;
  const applys = await db.findAll`select * from CarsApply where carId = ${id}`;
  res.send({
    success: true,
    message: "获取车辆申请成功!",
    data: applys,
  });
});

// 获取所有车辆申请记录
router.post("/user/cars/all", async (req, res) => {
  const { limit = 4, offset = 0, status = "全部" } = req.body;
  let total = 0;
  let applys = [];
  if (status === "待审批") {
    applys = await db.findAll`SELECT a.*, r.model, r.plateNumber
    FROM CarsApply a
    INNER JOIN Cars r ON a.carId = r.id where a.status = "待审批" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from CarsApply a
    INNER JOIN Cars r ON a.carId = r.id where a.status = "待审批"`
    ).total;
  } else if (status === "已通过") {
    applys = await db.findAll`SELECT a.*, r.model, r.plateNumber
    FROM CarsApply a
    INNER JOIN Cars r ON a.carId = r.id where a.status = "已通过" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from CarsApply a
    INNER JOIN Cars r ON a.carId = r.id where a.status = "已通过"`
    ).total;
  } else if (status === "未通过") {
    applys = await db.findAll`SELECT a.*, r.model, r.plateNumber
    FROM CarsApply a
    INNER JOIN Cars r ON a.carId = r.id where a.status = "未通过" order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from CarsApply a
    INNER JOIN Cars r ON a.carId = r.id where a.status = "未通过"`
    ).total;
  } else {
    applys = await db.findAll`SELECT a.*, r.model, r.plateNumber
    FROM CarsApply a
    INNER JOIN Cars r ON a.carId = r.id order by id desc limit ${limit} offset ${offset}`;
    total = (
      await db.findOne`select count(*) as total from CarsApply a
    INNER JOIN Cars r ON a.carId = r.id`
    ).total;
  }
  res.send({
    success: true,
    message: "获取车辆使用记录成功!",
    data: applys,
    total,
  });
});

// 车辆申请审批
router.put("/user/cars/apply/:id", async (req, res) => {
  const userId = await getUserId(req);
  const { id } = req.params;
  // 审批三种状态为：已通过、未通过、待审批
  const { status, reason } = req.body;
  // 判断权限
  const org = "行政部门";
  if (!(await hasPermission(userId, org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  if (status === "已通过") {
    await db.run`update CarsApply set status = ${status} where id = ${id}`;
  } else {
    await db.run`update CarsApply set status = ${status},approvalReason = ${reason} where id = ${id}`;
  }
  const apply = await db.findOne`select * from CarsApply where id = ${id}`;
  return res.send({
    success: true,
    message: "审批完成!",
    data: apply,
  });
});

// ----------------------资产信息---------------------------------------

// ----------------------资产信息---------------------------------------

// 发送消息
router.post("/user/chatRecord/:id", async (req, res) => {
  // 接收者 id
  const { id } = req.params;
  // 发送者 id
  const userId = await getUserId(req);
  const { content } = req.body;
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const messageId =
    await db.insert`insert into ChatRecords(postId, getId, content, createTime) values(${userId}, ${id}, ${content}, ${createTime})`;
  const message =
    await db.findOne`select * from ChatRecords where id = ${messageId}`;
  return res.send({
    success: true,
    message: "发送成功!",
    data: message,
  });
});

// 获取除自己以外的所有用户
router.get("/user/persons", async (req, res) => {
  const userId = await getUserId(req);
  const persons =
    await db.findAll`select * from Person where id != ${userId} and demission = "否"`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: persons,
  });
});

// 按照姓名模糊查询除自己以外的用户
router.get("/user/persons/:name", async (req, res) => {
  const { name } = req.params;
  const userId = await getUserId(req);
  const persons =
    await db.findAll`select * from Person where name like ${`%${name}%`} and id != ${userId} and demission = "否"`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: persons,
  });
});

// 获取当前登录用户和某个用户的聊天记录
router.get("/user/chatRecords/:id", async (req, res) => {
  // 接收者 id
  const { id } = req.params;
  // 发送者 id
  const userId = await getUserId(req);
  // 登录用户和接收者的聊天记录
  const messages =
    await db.findAll`select * from ChatRecords where (postId = ${id} and getId = ${userId} or postId = ${userId} and getId = ${id}) order by id asc`;
  messages.map((item) => {
    if (item.postId === userId) {
      item["type"] = "post";
    } else {
      item["type"] = "get";
    }
    return item;
  });
  return res.send({
    success: true,
    message: "获取成功!",
    data: messages,
  });
});

// 删除登录用户和某个用户的所有聊天记录
router.delete("/user/chatRecords/:id", async (req, res) => {
  // 接收者 id
  const { id } = req.params;
  // 发送者 id
  const userId = await getUserId(req);
  await db.run`delete from ChatRecords where (postId = ${id} and getId = ${userId} or postId = ${userId} and getId = ${id})`;
  return res.send({
    success: true,
    message: "删除成功!",
  });
});

// 模糊查询通讯录中的邮箱账号
router.get("/user/email/:email", async (req, res) => {
  const { email } = req.params;
  const userId = await getUserId(req);
  const contacts =
    await db.findAll`select * from Person t1, Contacts t2 where t1.id = t2.personId and demission = "否" and personId != ${userId} and email like ${`%${email}%`}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: contacts,
  });
});

// 发送邮件
router.post("/user/email/:id", async (req, res) => {
  // 接收者 id
  const { id } = req.params;
  // 发送者 id
  const userId = await getUserId(req);
  const { title, content } = req.body;
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const postName =
    await db.findOne`select name from Person where id = ${userId}`;
  const getName = await db.findOne`select name from Person where id = ${id}`;
  if (!getName?.name) {
    return res.send({
      success: false,
      message: "该用户不存在!",
    });
  }
  const emailId =
    await db.insert`insert into Emails(postId, getId,postName,getName, title, content, createTime) values(${userId}, ${id}, ${postName.name}, ${getName.name}, ${title}, ${content}, ${createTime})`;
  const email = await db.findOne`select * from Emails where id = ${emailId}`;

  // 将该条记录加入到全局收件里面
  const post = await db.findOne`select * from Person where id = ${userId}`;
  await db.run`insert into GlobalReceive (emailId, path, getId, postId, postName, avatar) values (${emailId},"/message_management_module/inbox", ${id}, ${userId}, ${post.name}, "https://img.js.design/assets/img/659365d356fd7ef9754a0c59.png#825fb7a99647f2d2c415e5b0985a8821"`;

  return res.send({
    success: true,
    message: "发送成功!",
    data: email,
  });
});

// 获取所有 email 不为空且不包含自己的用户信息
router.get("/user/getAllUsersWithEmail", async (req, res) => {
  const userId = await getUserId(req);
  const users =
    await db.findAll`select * from Person t1, Contacts t2 where t1.id = t2.personId and t1.id != ${userId} and t2.email != "" and demission = "否"`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: users,
  });
});

// 获取当前登录用户收到的所有未删除的邮件
router.post("/user/getEmails", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 10, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from Emails where getId = ${userId} and isDelete !="已删除"`;
  const emails =
    await db.findAll`select * from Emails where getId = ${userId} and isDelete !="已删除" order by id desc limit ${limit} offset ${offset}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: emails,
    total: total,
  });
});

// 获取当前登录用户发送的所有未删除的邮件
router.post("/user/postEmails", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 10, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from Emails where postId = ${userId} and isDelete !="已删除"`;
  const emails =
    await db.findAll`select * from Emails where postId = ${userId} and isDelete !="已删除" order by id desc limit ${limit} offset ${offset}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: emails,
    total: total,
  });
});

// 将登录用户的单个收件箱中的邮件放入垃圾箱
router.delete("/user/email/:id", async (req, res) => {
  // 要删除的邮件 id
  const { id } = req.params;
  await db.run`update Emails set isDelete = "已删除" where id = ${id}`;
  return res.send({
    success: true,
    message: "删除成功!",
  });
});

// 获取登录用户已放入垃圾箱的收件
router.post("/user/deleteGetEmails", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 10, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from Emails where getId = ${userId} and isDelete = "已删除"`;
  const emails =
    await db.findAll`select * from Emails where getId = ${userId} and isDelete = "已删除" order by id desc limit ${limit} offset ${offset}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: emails,
    total: total,
  });
});

// 获取登录用户已放入垃圾箱的发件
router.post("/user/deletePostEmails", async (req, res) => {
  const userId = await getUserId(req);
  const { limit = 10, offset = 0 } = req.body;
  const { total } =
    await db.findOne`select count(*) as total from Emails where postId = ${userId} and isDelete = "已删除"`;
  const emails =
    await db.findAll`select * from Emails where postId = ${userId} and isDelete = "已删除" order by id desc limit ${limit} offset ${offset}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: emails,
    total: total,
  });
});

// 从垃圾箱中确认删除
router.delete("/user/deleteEmails/:id", async (req, res) => {
  // 要删除的邮件 id
  const { id } = req.params;
  await db.run`delete from Emails where id = ${id}`;
  return res.send({
    success: true,
    message: "删除成功!",
  });
});

// 从垃圾箱中恢复
router.put("/user/deleteEmails/:id", async (req, res) => {
  // 要恢复的邮件 id
  const { id } = req.params;
  await db.run`update Emails set isDelete = "未删除" where id = ${id}`;
  return res.send({
    success: true,
    message: "恢复成功!",
  });
});

// 增加新用户
router.post("/user/register", async (req, res) => {
  const userId = await getUserId(req);
  // 判断权限
  const _org = "系统管理员";
  if (!(await hasPermission(userId, _org))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { name, sex, duty, description, org, username } = req.body;
  let { password } = req.body;
  password = makePassword(password);
  const exist =
    await db.findOne`select * from Person where username = ${username}`;
  if (exist) {
    return res.send({
      success: false,
      message: "用户名已存在!",
      errors: {
        username: ["用户名已存在!"],
      },
    });
  }
  const personId =
    await db.insert`insert into Person(name, sex, duty, description, org, username, password) values(${name}, ${sex}, ${duty}, ${description}, ${org}, ${username}, ${password})`;
  const person = await db.findOne`select * from Person where id = ${personId}`;
  return res.send({
    success: true,
    message: "注册成功!",
    data: person,
  });
});

// 获取所有用户信息 (包括已离职和自己)
router.get("/user/allPerson", async (req, res) => {
  const userId = await getUserId(req);
  // 判断权限
  if (!(await hasPermission(userId, "系统管理员"))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const persons = await db.findAll`select * from Person`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: persons,
  });
});

// 获取所有部门
router.get("/user/allOrgs", async (req, res) => {
  const orgs = await db.findAll`select * from Orgs`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: orgs,
  });
});

// 删除用户
router.delete("/user/delete/:id", async (req, res) => {
  const userId = await getUserId(req);
  // 判断权限
  if (!(await hasPermission(userId, "系统管理员"))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { id } = req.params;
  await db.run`delete from Person where id = ${id}`;
  return res.send({
    success: true,
    message: "删除成功!",
  });
});

// 获取某个用户的信息
router.get("/user/getInfo/:id", async (req, res) => {
  const userId = await getUserId(req);
  // 判断权限
  if (
    !(
      (await hasPermission(userId, "系统管理员")) ||
      (await hasPermission(userId, "人事部门"))
    )
  ) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { id } = req.params;
  const person = await db.findOne`select * from Person where id = ${id}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: person,
  });
});

// 修改用户部门
router.put("/user/changeOrg", async (req, res) => {
  const userId = await getUserId(req);
  // 判断权限
  if (!(await hasPermission(userId, "系统管理员"))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { org } = req.body;
  const personId =
    await db.run`update Person set org = ${org} where id = ${userId}`;
  const person = await db.findOne`select * from Person where id = ${personId}`;
  return res.send({
    success: true,
    message: "修改成功!",
    data: person,
  });
});

// 修改自己的账号和密码
router.put("/user/changeMyInfo", async (req, res) => {
  const userId = await getUserId(req);
  let { newUsername, oldPassword, password } = req.body;
  // 验证 newUsername 是否存在
  const exist =
    await db.findOne`select * from Person where username = ${newUsername} and id != ${userId}`;
  if (exist) {
    return res.send({
      success: false,
      message: "用户名已存在!",
      errors: {
        newUsername: ["该用户名已存在!"],
      },
    });
  }
  // 验证旧密码是否正确
  const person = await db.findOne`select * from Person where id = ${userId}`;
  if (!checkPassword(oldPassword, person.password)) {
    return res.send({
      success: false,
      message: "旧密码不正确!",
      errors: {
        oldPassword: ["原密码错误!"],
      },
    });
  }
  console.log("makePassword前", password);
  password = makePassword(password);
  console.log("makePassword后", password);
  await db.run`update Person set password = ${password} where id = ${userId}`;
  const person2 = await db.findOne`select * from Person where id = ${userId}`;
  return res.send({
    success: true,
    message: "修改成功!",
    data: person2,
  });
});

// 修改用户信息
router.put("/user/changeInfo:id", async (req, res) => {
  const userId = await getUserId(req);
  // 判断权限
  if (!(await hasPermission(userId, "人事部门"))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { name, sex, duty, description, org } = req.body;
  const { id } = req.params;
  const personId =
    await db.run`update Person set name = ${name}, sex = ${sex}, duty = ${duty}, description = ${description}, org = ${org} where id = ${id}`;
  const person = await db.findOne`select * from Person where id = ${personId}`;
  return res.send({
    success: true,
    message: "修改成功!",
    data: person,
  });
});

// 申请转正
router.put("/user/positiveApply", async (req, res) => {
  const userId = await getUserId(req);
  const reason = req.body.reason;
  const person = await db.findOne`select * from Person where id = ${userId}`;
  if (person.formal !== "否") {
    return res.send({
      success: false,
      message: "已经是正式员工!",
    });
  }
  // 审批三种状态为：已通过、未通过、待审批
  const exist =
    await db.findOne`select * from PositiveApply where id = ${userId} and status = "待审批"`;
  if (exist) {
    return res.send({
      success: false,
      message: "已有申请正在审批中!",
    });
  }
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const applyId =
    await db.insert`insert into PositiveApply (personId,applyReason, status,createTime) values (${userId},${reason}, "待审批", ${createTime})`;
  const apply =
    await db.findOne`select * from PositiveApply where id = ${applyId}`;
  return res.send({
    success: true,
    message: "申请成功,等待审批!",
    data: apply,
  });
});

// 获取当前登录用户的申请转正记录
router.get("/user/positiveApply", async (req, res) => {
  const userId = await getUserId(req);
  const applys =
    await db.findAll`select * from PositiveApply where personId = ${userId}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: applys,
  });
});

// 转正申请审批
router.put("/user/positiveApply/:id", async (req, res) => {
  const userId = await getUserId(req);
  // 权限判断
  if (!(await hasPermission(userId, "管理层"))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { id } = req.params;
  const { status, reason } = req.body;
  // 审批三种状态为：已通过、未通过、待审批
  if (status === "已通过") {
    await db.run`update PositiveApply set status = ${status} where id = ${id}`;
  } else {
    await db.run`update PositiveApply set status = ${status}, approvalReason = ${reason} where id = ${id}`;
  }
  const apply = await db.findOne`select * from PositiveApply where id = ${id}`;
  return res.send({
    success: true,
    message: "审批成功!",
    data: apply,
  });
});

// 离职申请
router.put("/user/retireApply", async (req, res) => {
  const userId = await getUserId(req);
  const reason = req.body.reason;
  const person = await db.findOne`select * from Person where id = ${userId}`;
  if (person.demission !== "否") {
    return res.send({
      success: false,
      message: "您已经是离职员工!",
    });
  }
  // 审批三种状态为：已通过、未通过、待审批
  const exist =
    await db.findOne`select * from PositiveApply where id = ${userId} and status = "待审批"`;
  if (exist) {
    return res.send({
      success: false,
      message: "已有离职申请正在审批中!",
    });
  }
  const createTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const applyId =
    await db.insert`insert into RetireApply (personId,applyReason, status,createTime) values (${userId},${reason}, "待审批", ${createTime})`;
  const apply =
    await db.findOne`select * from RetireApply where id = ${applyId}`;
  return res.send({
    success: true,
    message: "申请成功,等待审批!",
    data: apply,
  });
});

// 获取当前登录用户的离职申请记录
router.get("/user/retireApply", async (req, res) => {
  const userId = await getUserId(req);
  const applys =
    await db.findAll`select * from RetireApply where personId = ${userId}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: applys,
  });
});

// 离职申请审批
router.put("/user/retireApply/:id", async (req, res) => {
  const userId = await getUserId(req);
  // 权限判断
  if (!(await hasPermission(userId, "管理层"))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const { id } = req.params;
  const { status, reason } = req.body;
  // 审批三种状态为：已通过、未通过、待审批
  if (status === "已通过") {
    await db.run`update RetireApply set status = ${status} where id = ${id}`;
  } else {
    await db.run`update RetireApply set status = ${status}, approvalReason = ${reason} where id = ${id}`;
  }
  const apply = await db.findOne`select * from RetireApply where id = ${id}`;
  return res.send({
    success: true,
    message: "审批成功!",
    data: apply,
  });
});

// 上班打卡
router.put("/user/attendance/arrive", async (req, res) => {
  const userId = await getUserId(req);
  const today = dayjs().format("YYYY-MM-DD");

  // 查询今天是否已经有该用户的上班打卡记录
  const existingAttendance =
    await db.findOne`select * from Attendance where personId = ${userId} and arriveTime LIKE ${`${today}%`}`;

  if (existingAttendance) {
    return res.send({
      success: false,
      message: "今天已经打过卡了，无法重复打卡",
      data: existingAttendance,
    });
  } else {
    const arriveTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
    const attendanceId =
      await db.insert`insert into Attendance (personId, arriveTime) values (${userId}, ${arriveTime})`;
    const attendance =
      await db.findOne`select * from Attendance where id = ${attendanceId}`;
    return res.send({
      success: true,
      message: "打卡成功!",
      data: attendance,
    });
  }
});

// 下班打卡
router.put("/user/attendance/leave", async (req, res) => {
  const userId = await getUserId(req);
  const today = dayjs().format("YYYY-MM-DD");

  // 查询今天是否已经有该用户的下班打卡记录
  const existingAttendance =
    await db.findOne`select * from Attendance where personId = ${userId} and leaveTime LIKE ${`${today}%`}`;
  if (existingAttendance) {
    return res.send({
      success: false,
      message: "今天已经打过卡了，无法重复打卡",
      data: existingAttendance,
    });
  } else {
    const leaveTime = dayjs().format("YYYY-MM-DD HH:mm:ss");
    const attendanceId =
      await db.insert`insert into Attendance (personId, leaveTime) values (${userId}, ${leaveTime})`;
    const attendance =
      await db.findOne`select * from Attendance where id = ${attendanceId}`;
    return res.send({
      success: true,
      message: "打卡成功!",
      data: attendance,
    });
  }
});

// 获取当前登录用户的今日打卡记录
router.get("/user/attendance", async (req, res) => {
  const userId = await getUserId(req);
  const today = dayjs().format("YYYY-MM-DD");
  const attendances =
    await db.findAll`select * from Person t1, Attendance t2 where t1.id = t2.personId and t2.personId = ${userId} and t2.arriveTime LIKE ${`${today}%`}`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: attendances,
  });
});

// 查询所有员工的今日打卡情况
router.get("/user/attendance/all", async (req, res) => {
  // 判断权限
  const userId = await getUserId(req);
  if (!(await hasPermission(userId, "人事部门"))) {
    return res.send({
      success: false,
      message: "权限不足!",
    });
  }
  const today = dayjs().format("YYYY-MM-DD");
  const attendances =
    await db.findAll`SELECT * FROM Person t1 LEFT JOIN Attendance t2 ON t1.id = t2.personId AND t2.arriveTime LIKE ${`${today}%`}%;`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: attendances,
  });
});

// 获取当前登录用户未读的全局收件表
router.get("/user/globalReceive", async (req, res) => {
  const userId = await getUserId(req);
  const emails =
    await db.findAll`SELECT a.*, b.title, b.content, b.createTime FROM GlobalReceive a INNER JOIN Emails b ON a.emailId = b.id WHERE a.getId = ${userId} AND isRead = "未读" order by id desc`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: emails,
  });
});

// 按照 id 将未读的全局收件表标记为已读
router.put("/user/globalReceive/:id", async (req, res) => {
  const id = req.params.id;
  await db.run`UPDATE GlobalReceive SET isRead = "已读" WHERE id = ${id}`;
  const email = await db.findOne`SELECT * FROM GlobalReceive WHERE id = ${id}`;
  return res.send({
    success: true,
    message: "标记成功!",
    data: email,
  });
});

// 获取当前登录用户未读的全局信息表
router.get("/user/globalInfo", async (req, res) => {
  const userId = await getUserId(req);
  const infos =
    await db.findAll`SELECT GlobalInfo.* FROM GlobalInfo JOIN GlobalInfoRead ON GlobalInfo.id = GlobalInfoRead.globalInfoId WHERE GlobalInfoRead.personId = ${userId} AND GlobalInfoRead.isRead = '未读' order by id desc`;
  return res.send({
    success: true,
    message: "获取成功!",
    data: infos,
  });
});

// 按照 id 将未读的全局信息表标记为已读
router.put("/user/globalInfo/:id", async (req, res) => {
  const id = req.params.id;
  const userId = await getUserId(req);
  await db.run`UPDATE GlobalInfoRead SET isRead = '已读' WHERE globalInfoId = ${id} and personId = ${userId}`;
  const info = await db.findOne`SELECT GlobalInfo.*
    FROM GlobalInfo
    JOIN GlobalInfoRead ON GlobalInfo.id = GlobalInfoRead.globalInfoId
    WHERE GlobalInfo.id = ${id}`;
  return res.send({
    success: true,
    message: "标记成功!",
    data: info,
  });
});

export default (app) => {
  app.use("/api", router);
};
